﻿/******************************/
/****** Drop Database *********/
/******************************/ 
USE [NHibernate]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[NHFAuto].[FK_Employee_Store]') AND parent_object_id = OBJECT_ID(N'[NHFAuto].[Employee]'))
ALTER TABLE [NHFAuto].[Employee] DROP CONSTRAINT [FK_Employee_Store]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[NHFAuto].[FK_StoreProduct_Product]') AND parent_object_id = OBJECT_ID(N'[NHFAuto].[StoreProduct]'))
ALTER TABLE [NHFAuto].[StoreProduct] DROP CONSTRAINT [FK_StoreProduct_Product]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[NHFAuto].[FK_StoreProduct_Store]') AND parent_object_id = OBJECT_ID(N'[NHFAuto].[StoreProduct]'))
ALTER TABLE [NHFAuto].[StoreProduct] DROP CONSTRAINT [FK_StoreProduct_Store]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[NHFAuto].[Employee]') AND type in (N'U'))
DROP TABLE [NHFAuto].[Employee]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[NHFAuto].[StoreProduct]') AND type in (N'U'))
DROP TABLE [NHFAuto].[StoreProduct]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[NHFAuto].[Product]') AND type in (N'U'))
DROP TABLE [NHFAuto].[Product]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[NHFAuto].[Store]') AND type in (N'U'))
DROP TABLE [NHFAuto].[Store]
GO

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'NHFAuto')
DROP SCHEMA [NHFAuto]
GO

/******************************/
/****** Create Database *******/
/******************************/ 
USE [NHibernate]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE SCHEMA [NHFAuto] AUTHORIZATION [dbo]
GO

CREATE TABLE [NHFAuto].[Store](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_Store] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [NHFAuto].[StoreProduct](
	[ProductId] [int] NOT NULL,
	[StoreId] [int] NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [NHFAuto].[Product](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Price] [money] NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [NHFAuto].[Employee](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[LastName] [nvarchar](100) NOT NULL,
	[FirstName] [nvarchar](100) NOT NULL,
	[AddressLine] [nvarchar](100) NOT NULL,
	[PostCode] [nvarchar](100) NOT NULL,
	[City] [nvarchar](100) NOT NULL,
	[State] [nvarchar](100) NOT NULL,
	[Country] [nvarchar](100) NOT NULL,
	[StoreId] [int] NOT NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [NHFAuto].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Store] FOREIGN KEY([StoreId])
REFERENCES [NHFAuto].[Store] ([Id])
GO

ALTER TABLE [NHFAuto].[Employee] CHECK CONSTRAINT [FK_Employee_Store]
GO


ALTER TABLE [NHFAuto].[StoreProduct]  WITH CHECK ADD  CONSTRAINT [FK_StoreProduct_Product] FOREIGN KEY([ProductId])
REFERENCES [NHFAuto].[Product] ([Id])
GO

ALTER TABLE [NHFAuto].[StoreProduct] CHECK CONSTRAINT [FK_StoreProduct_Product]
GO

ALTER TABLE [NHFAuto].[StoreProduct]  WITH CHECK ADD  CONSTRAINT [FK_StoreProduct_Store] FOREIGN KEY([StoreId])
REFERENCES [NHFAuto].[Store] ([Id])
GO

ALTER TABLE [NHFAuto].[StoreProduct] CHECK CONSTRAINT [FK_StoreProduct_Store]
GO